package com.youaishujuhui.devops.web.dao.manage;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.stereotype.Service;

import com.mysql.jdbc.StringUtils;
import com.youaishujuhui.devops.web.domain.manage.SysRole;
import com.youaishujuhui.devops.web.domain.manage.SysUser;

@Service
public class SysUserDao {
	
	@Resource(name = "nutDao")
    private Dao nutDao;
	
	public SysUser getByUserName(String userName) {
		SysUser u = nutDao.fetch(SysUser.class, userName);
		return u;
	}
	
	public SysUser fetchRoles(SysUser user) {
		SysUser u = nutDao.fetchLinks(user, "roles");
		return u;
	}

	public SysUser insert(String userName) {
		SysUser u = new SysUser();
		u.setUserName(userName);
		return nutDao.insert(u);
	}

	public int total(Map<String, Object> param) {
		Cnd cnd = Cnd.NEW();
		
		if (false == StringUtils.isNullOrEmpty((String) param.get("searchName"))) {
			cnd = Cnd.where("userName", "like", "%" + param.get("searchName") + "%");
		}
		
		return nutDao.count(SysUser.class, cnd);
	}

	public List<SysUser> query(Map<String, Object> param) {
		Cnd cnd = Cnd.NEW();
		
		if (false == StringUtils.isNullOrEmpty((String) param.get("searchName"))) {
			cnd = Cnd.where("userName", "like", "%" + param.get("searchName") + "%");
		}

		if (false == StringUtils.isNullOrEmpty((String) param.get("sort"))) {
			String sort = (String) param.get("sort");
			
			if (false == StringUtils.isNullOrEmpty((String) param.get("sort"))) {
				String order = (String) param.get("order");
				
				if (order.equals("asc")) {
					//orderBy = Cnd.orderBy().asc(sort);
					
					cnd.asc(sort);
				} else {
					//orderBy = Cnd.orderBy().desc(sort);
					
					cnd.desc(sort);
				}
			} else {
				//orderBy = Cnd.orderBy().asc(sort);
				
				cnd.asc(sort);
			}
		}
		
		int offset = (int) param.get("offset");
		int limit = (int) param.get("limit");
		Pager pager = new Pager(offset / limit + 1, limit);
		
		
		return nutDao.query(SysUser.class, cnd, pager);
		
	}

	public List<SysRole> getRolesList(Integer userId) {
		Sql sql = Sqls.create("SELECT a.roleId AS roleId,a.name AS NAME,a.code AS `code`,IF(b.uId IS NULL,FALSE ,TRUE ) AS checked  FROM t_sys_role a\r\n" + 
				"		LEFT OUTER JOIN t_user_role b\r\n" + 
				"		ON a.roleId = b.roleId AND b.uId=@uId\r\n" + 
				"		ORDER BY a.roleId");
	    sql.params().set("uId", userId);
	    
	    sql.setCallback(new SqlCallback() {
	        public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
	            List<SysRole> list = new LinkedList<SysRole>();
	            while (rs.next()) {
	            	SysRole r = new SysRole();
	            	r.setRoleId(rs.getInt("roleId"));
	            	r.setName(rs.getString("name"));
	            	r.setCode(rs.getString("code"));
	            	r.setChecked(rs.getBoolean("checked"));
	            	
	                list.add(r);
	            }
	            	
	            return list;
	        }
	    });
	    
	    nutDao.execute(sql);
	    return sql.getList(SysRole.class);
	    // Nutz内置了大量回调, 请查看Sqls.callback的属性
	}

	public SysUser findById(Integer id) {
		return nutDao.fetch(SysUser.class, id);
	}
	
	public void clearRoles(SysUser user) {
		nutDao.clearLinks(user, "roles");
	}

	public void insertRelation(SysUser user) {
		nutDao.insertRelation(user, "roles");
	}
}
